package com.platform.batch.base.component.reader;

import com.sun.xml.internal.stream.XMLInputFactoryImpl;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.batch.item.ItemStreamException;
import org.springframework.batch.item.file.LineCallbackHandler;
import org.springframework.batch.item.file.ResourceAwareItemReaderItemStream;
import org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.core.io.Resource;
import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;

import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamConstants;
import javax.xml.stream.XMLStreamReader;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

/**
 * Description: Excel Reader
 *
 * @author libin
 * Created on 2019/6/20
 **/
@SuppressWarnings({"NullableProblems"})
@Slf4j
public class ExcelStreamingItemReader<T> extends AbstractItemCountingItemStreamItemReader<T>
        implements ResourceAwareItemReaderItemStream<T>, InitializingBean {

    private Resource resource;

    private InputStream sheetInputStream;
    private XMLStreamReader sheetStreamReader;
    private SharedStringsTable sharedStringsTable;

    private boolean noInput = false;
    private int currentRowNum = 0;
    private Boolean r1c1;
    private String[] header;
    private Map<String, String> headerMapping;

    @Setter
    private int linesToSkip = 0;
    @Setter
    private LineCallbackHandler skippedLinesCallback;

    @Setter
    private Function<Map<String, String>, T> convert;

    public ExcelStreamingItemReader() {
        super();
        this.setName(ClassUtils.getShortName(this.getClass()));
    }

    @Override
    public void setResource(Resource resource) {
        this.resource = resource;
    }

    @Override
    protected T doRead() throws Exception {
        int end = Integer.MAX_VALUE;
        if (currentRowNum == end) {
            return null;
        }
        if (noInput) {
            return null;
        }
        Map<String, String> resultMap;
        while (sheetStreamReader.hasNext()) {
            final int event = sheetStreamReader.next();
            if (event == XMLStreamConstants.START_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                currentRowNum = Integer.parseInt(sheetStreamReader.getAttributeValue(null, "r")) - 1;
                resultMap = new HashMap<>(16);
                while (sheetStreamReader.hasNext()) {
                    final int event1 = sheetStreamReader.next();
                    if (event1 == XMLStreamConstants.START_ELEMENT && "c".equals(sheetStreamReader.getLocalName())) {
                        final String columnLabel = getColumnLabel(sheetStreamReader.getAttributeValue(null, "r"));
                        if (headerMapping == null) {
                            initHeaderMapping();
                        }
                        final String key = headerMapping.get(columnLabel);
                        resultMap.put(key, getCellStringValue());
                    } else if (event1 == XMLStreamConstants.END_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                        return convert.apply(resultMap);
                    }
                }
            }
        }
        return null;
    }

    @Override
    protected void doOpen() {
        Assert.notNull(this.resource, "Input resource must be set");
        this.noInput = true;
        if (!this.resource.exists()) {
            throw new IllegalStateException("Input resource must exist (reader is in 'strict' mode): "
                    + this.resource);
        }

        if (!this.resource.isReadable()) {
            throw new IllegalStateException("Input resource must be readable (reader is in 'strict' mode): "
                    + this.resource);
        }
        initWorkbookAndSheet();
        for (int i = 1; i < linesToSkip; i++) {
            if (skippedLinesCallback != null) {
                skippedLinesCallback.handleLine(String.join(",", header));
            }
        }
        this.noInput = false;
    }

    @Override
    protected void doClose() {
        freeResource(sheetInputStream);
        freeResource(sheetStreamReader);
    }

    @Override
    public void afterPropertiesSet() {
        Assert.notNull(this.convert, "convert must be set");
    }

    private void initWorkbookAndSheet() throws ItemStreamException {
        try {
            final OPCPackage opcPackage = OPCPackage.open(this.resource.getInputStream());
            final XSSFReader xssfReader = new XSSFReader(opcPackage);
            XSSFWorkbook wb = new XSSFWorkbook(opcPackage);
            // 过去当前sheet个数
            int count = wb.getNumberOfSheets();
            if (count == 0) {
                throw new RuntimeException("模板文件不存在默认的sheet");
            }
            // 默认约定取第一个sheet
            String sheetName = wb.getSheetName(0);
            XSSFSheet xssfSheet = wb.getSheet(sheetName);
            String rId = wb.getRelationId(xssfSheet);
            sharedStringsTable = xssfReader.getSharedStringsTable();
            sheetInputStream = xssfReader.getSheet(rId);
            // 不再通过工厂来定位实现，解决WebService引入的XMLInputFactory自定义实现导致的冲突问题
            final XMLInputFactory xmlInputFactory = new XMLInputFactoryImpl();
            sheetStreamReader = xmlInputFactory.createXMLStreamReader(sheetInputStream);

            //fast forward to the start row, which may not immediately follow header row
            if (header == null) {
                headerMapping = new HashMap<>(16);
                outerLoop:
                while (sheetStreamReader.hasNext()) {
                    if (sheetStreamReader.next() == XMLStreamConstants.START_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                        final int rowNum = Integer.parseInt(sheetStreamReader.getAttributeValue(null, "r"));
                        if (linesToSkip == rowNum) {
                            final List<String> headerVals = new ArrayList<>();
                            while (sheetStreamReader.hasNext()) {
                                final int event = sheetStreamReader.next();
                                if (event == XMLStreamConstants.START_ELEMENT && "c".equals(sheetStreamReader.getLocalName())) {
                                    final String label = getColumnLabel(sheetStreamReader.getAttributeValue(null, "r"));
                                    final String value = getCellStringValue();
                                    String val = value;
                                    if (null != value) {
                                        val = StringUtils.replace(val, "*", StringUtils.EMPTY);
                                    }
                                    headerVals.add(val);
                                    headerMapping.put(label, val);
                                } else if (event == XMLStreamConstants.END_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                                    header = headerVals.toArray(new String[0]);
                                    currentRowNum = rowNum - 1;
                                    break outerLoop;
                                }
                            }
                        }
                    }
                }
            }

            //fast forward to the start row, which may not immediately follow header row
            while (currentRowNum < linesToSkip - 1 && sheetStreamReader.hasNext()) {
                if (sheetStreamReader.next() == XMLStreamConstants.START_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                    currentRowNum = Integer.parseInt(sheetStreamReader.getAttributeValue(null, "r")) - 1;
                } else if (sheetStreamReader.next() == XMLStreamConstants.END_ELEMENT && "row".equals(sheetStreamReader.getLocalName())) {
                    if (currentRowNum >= linesToSkip - 1) {
                        break;
                    }
                }
            }
        } catch (Exception e) {
            throw new ItemStreamException(e);
        }finally {
            close();
        }

    }


    private void freeResource(Object resource) {
        if (resource != null) {
            try {
                if (resource instanceof InputStream) {
                    ((InputStream) resource).close();
                }

                if (resource instanceof XMLStreamReader) {
                    ((XMLStreamReader) resource).close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    private String getCellStringValue() throws Exception {
        String result = null;
        final String cellType = sheetStreamReader.getAttributeValue(null, "t");
        while (sheetStreamReader.hasNext()) {
            final int event = sheetStreamReader.next();
            if (event == XMLStreamConstants.START_ELEMENT && "v".equals(sheetStreamReader.getLocalName())) {
                result = sheetStreamReader.getElementText();
                if ("s".equals(cellType)) {
                    final int idx = Integer.parseInt(result);
                    result = sharedStringsTable.getItemAt(idx).getString();
                }
            } else if (event == XMLStreamConstants.START_ELEMENT && "t".equals(sheetStreamReader.getLocalName())) {
                result = sheetStreamReader.getElementText();
            } else if (event == XMLStreamConstants.END_ELEMENT && "c".equals(sheetStreamReader.getLocalName())) {
                break;
            }
        }
        return result;
    }

    private String getColumnLabel(final String cellPoint) {
        // a R1C1 notation: R1234C567
        if (r1c1 == null) {
            final char[] chars = cellPoint.toCharArray();
            r1c1 = chars[0] == 'R' && Character.isDigit(chars[1]);
            return getColumnLabel(cellPoint);
        } else if (r1c1) {
            return cellPoint.substring(cellPoint.indexOf('C') + 1);
        } else {
            for (int i = 0; i < cellPoint.length(); ++i) {
                if (Character.isDigit(cellPoint.charAt(i))) {
                    return cellPoint.substring(0, i);
                }
            }
            return cellPoint;
        }
    }

    private void initHeaderMapping() {
        if (r1c1) {
            for (int index = 0; index < header.length; ++index) {
                headerMapping.put(String.valueOf(index + 1), header[index]);
            }
        } else {
            for (int index = 0; index < header.length; ++index) {
                headerMapping.put(getColumnLabelByPosition(index), header[index]);
            }
        }
    }

    private static String getColumnLabelByPosition(final long index) {
        final char[] ret = new char[64];
        for (int i = 0; i < ret.length; ++i) {
            final int digit = ret.length - i - 1;
            final long test = index - powerDown(i + 1);
            if (test < 0) {
                break;
            }
            ret[digit] = toChar(test / (long) (Math.pow(26, i)));
        }
        return new String(ret);
    }

    private static char toChar(final long num) {
        return (char) ((num % 26) + 65);
    }

    private static long powerDown(int limit) {
        long acc = 0;
        while (limit > 1) {
            acc += Math.pow(26, limit-- - 1);
        }
        return acc;
    }
}
